package com.youan.log.modules.audit.dataanalysis;

import com.youan.log.common.threadtask.ThreadTaskProgressor;
import com.youan.log.modules.audit.entity.SdAttackSuccess;
import com.youan.log.modules.audit.service.ISdAttackSuccessService;
import org.springblade.core.tool.utils.SpringUtil;
import org.springframework.jdbc.core.JdbcTemplate;

import javax.sql.DataSource;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;

/**
 * 统计山东各地区主动攻击其他地区的成功次数统计表
 */
public class SdAttackSuccessDataExtract {

    private String date;
    private ThreadTaskProgressor taskProgressor;
    private ISdAttackSuccessService sdAttackSuccessService;
    private JdbcTemplate jdbcTemplate;
    private List<SdAttackSuccess> sdAttackSuccessList = new ArrayList<>();
    private int BATCH_SIZE = 5000;

    public SdAttackSuccessDataExtract(String date, ThreadTaskProgressor taskProgressor) {

        this.date = date;
        this.taskProgressor = taskProgressor;
        DataSource dataSource = SpringUtil.getBean(DataSource.class);
        this.jdbcTemplate = new JdbcTemplate(dataSource);
        sdAttackSuccessService = SpringUtil.getBean(ISdAttackSuccessService.class);

        taskProgressor.updateProgressTotal(taskProgressor.getProgressTotalNum() + getCount());
    }


    public void extract() {
        getSdAttackSuccessDataFromStatistical();
    }

    private void getSdAttackSuccessDataFromStatistical() {
        String sql = "SELECT\n" +
                "  date,\n" +
                "  attack_city,\n" +
                "  attacked_city,\n" +
                "  attacked_country,\n" +
                "  sum(attack_num) attack_num\n" +
                "FROM\n" +
                "  (\n" +
                "    SELECT\n" +
                "      visit_date date,\n" +
                "      IFNULL(source_city, '未知城市') attack_city,\n" +
                "      IFNULL(dest_city, '未知城市') attacked_city,\n" +
                "      dest_country attacked_country,\n" +
                "      sum(visit_number) attack_num\n" +
                "    FROM\n" +
                "      log_statistical\n" +
                "    WHERE\n" +
                "      source_ip IN (\n" +
                "        SELECT\n" +
                "          attacker_ip\n" +
                "        FROM\n" +
                "          log_attack_analysis\n" +
                "        WHERE\n" +
                "          province = '山东' and date = '" + this.date + "' \n" +
                "      ) and visit_date = '" + this.date + "'\n" +
                "    GROUP BY\n" +
                "      date,\n" +
                "      attack_city,\n" +
                "      attacked_city,\n" +
                "      attacked_country\n" +
                "    UNION ALL\n" +
                "      SELECT\n" +
                "        visit_date date,\n" +
                "        IFNULL(dest_city, '未知城市') attack_city,\n" +
                "        IFNULL(source_city, '未知城市') attacked_city,\n" +
                "        source_country attacked_country,\n" +
                "        sum(visit_number) attack_num\n" +
                "      FROM\n" +
                "        log_statistical\n" +
                "      WHERE\n" +
                "        dest_ip IN (\n" +
                "          SELECT\n" +
                "            attacker_ip\n" +
                "          FROM\n" +
                "            log_attack_analysis\n" +
                "          WHERE\n" +
                "            province = '山东' and date = '" + this.date + "'\n" +
                "        ) and visit_date = '" + this.date + "'\n" +
                "      GROUP BY\n" +
                "        date,\n" +
                "        attack_city,\n" +
                "        attacked_city,\n" +
                "        attacked_country\n" +
                "  ) a\n" +
                "GROUP BY\n" +
                "  date,\n" +
                "  attack_city,\n" +
                "  attacked_city,\n" +
                "  attacked_country";

        jdbcTemplate.query(sql, rs -> {

            SdAttackSuccess sdAttackSuccess = new SdAttackSuccess();
            LocalDate localDate = LocalDate.parse(rs.getString("date"), DateTimeFormatter.ofPattern("yyyy-MM-dd"));
            sdAttackSuccess.setDate(localDate);
            sdAttackSuccess.setAttackCity(rs.getString("attack_city"));
            sdAttackSuccess.setAttackedCity(rs.getString("attacked_city"));
            sdAttackSuccess.setAttackedCountry(rs.getString("attacked_country"));
            sdAttackSuccess.setAttackNum(rs.getInt("attack_num"));

            sdAttackSuccessList.add(sdAttackSuccess);
            if(sdAttackSuccessList.size() >= BATCH_SIZE) {
                sdAttackSuccessService.saveBatch(sdAttackSuccessList);
                taskProgressor.addProgressNum(sdAttackSuccessList.size());
                sdAttackSuccessList = new ArrayList<>();
            }
        });
        sdAttackSuccessService.saveBatch(sdAttackSuccessList);
        taskProgressor.addProgressNum(sdAttackSuccessList.size());
    }

    private int getCount() {
        String sql = "SELECT\n" +
                "   count(1)\n" +
                "FROM\n" +
                "   (\n" +
                "      SELECT\n" +
                "         date,\n" +
                "         attack_city,\n" +
                "         attacked_city,\n" +
                "         attacked_country\n" +
                "      FROM\n" +
                "         (\n" +
                "            SELECT\n" +
                "               visit_date date,\n" +
                "               IFNULL(source_city, '未知城市') attack_city,\n" +
                "               IFNULL(dest_city, '未知城市') attacked_city,\n" +
                "               dest_country attacked_country,\n" +
                "               sum(visit_number) attack_num\n" +
                "            FROM\n" +
                "               log_statistical\n" +
                "            WHERE\n" +
                "               source_ip IN (\n" +
                "                  SELECT\n" +
                "                     attacker_ip\n" +
                "                  FROM\n" +
                "                     log_attack_analysis\n" +
                "                  WHERE\n" +
                "                     province = '山东'\n" +
                "                  AND date = '2020-12-21'\n" +
                "               )\n" +
                "            AND visit_date = '2020-12-21'\n" +
                "            GROUP BY\n" +
                "               date,\n" +
                "               attack_city,\n" +
                "               attacked_city,\n" +
                "               attacked_country\n" +
                "            UNION ALL\n" +
                "               SELECT\n" +
                "                  visit_date date,\n" +
                "                  IFNULL(dest_city, '未知城市') attack_city,\n" +
                "                  IFNULL(source_city, '未知城市') attacked_city,\n" +
                "                  source_country attacked_country,\n" +
                "                  sum(visit_number) attack_num\n" +
                "               FROM\n" +
                "                  log_statistical\n" +
                "               WHERE\n" +
                "                  dest_ip IN (\n" +
                "                     SELECT\n" +
                "                        attacker_ip\n" +
                "                     FROM\n" +
                "                        log_attack_analysis\n" +
                "                     WHERE\n" +
                "                        province = '山东'\n" +
                "                     AND date = '2020-12-21'\n" +
                "                  )\n" +
                "               AND visit_date = '2020-12-21'\n" +
                "               GROUP BY\n" +
                "                  date,\n" +
                "                  attack_city,\n" +
                "                  attacked_city,\n" +
                "                  attacked_country\n" +
                "         ) a\n" +
                "      GROUP BY\n" +
                "         date,\n" +
                "         attack_city,\n" +
                "         attacked_city,\n" +
                "         attacked_country\n" +
                "   ) d";
        return jdbcTemplate.queryForObject(sql, Integer.class);
    }
}
